package repository

import (
	"context"
	"database/sql"
	"errors"
	"regexp"
	"time"

	"gitee.com/hp-huiw/family-expense-api/internal/constant"
	"gitee.com/hp-huiw/family-expense-api/internal/db"
	dbmodel "gitee.com/hp-huiw/family-expense-api/internal/db/model"
	"gitee.com/hp-huiw/my-go-core/util"
	jet "github.com/go-jet/jet/v2/mysql"
	"github.com/go-jet/jet/v2/qrm"
)

type JourneyIncomeExpenseDetail struct {
	ID                   string    `db:"id"`
	IncomeExpenseDate    time.Time `db:"income_expense_date"`
	Location             string    `db:"location"`
	Currency             string    `db:"currency"`
	Amount               float64   `db:"amount"`
	Type                 string    `db:"type"`
	PaymentType          string    `db:"payment_type"`
	Details              string    `db:"details"`
	BankID               *string   `db:"bank_id"`
	BankShortName        *string   `db:"bank_short_name"`
	BankCardID           *string   `db:"bank_card_id"`
	BankCardName         *string   `db:"bank_card_name"`
	BankCardIsCreditCard *bool     `db:"bank_card_is_credit_card"`
	JourneyID            *string   `db:"journey_id"`
	JourneyStartDate     *string   `db:"journey_start_date"`
	JourneyEndDate       *string   `db:"journey_end_date"`
	RegionTitle          *string   `db:"region_title"`
}

type JourneyIncomeExpenseFullAddressDetail struct {
	ID                   string     `db:"id"`
	IncomeExpenseDate    time.Time  `db:"income_expense_date"`
	Location             string     `db:"location"`
	Currency             string     `db:"currency"`
	Amount               float64    `db:"amount"`
	Type                 string     `db:"type"`
	PaymentType          string     `db:"payment_type"`
	Details              string     `db:"details"`
	BankID               *string    `db:"bank_id"`
	BankShortName        *string    `db:"bank_short_name"`
	BankCardID           *string    `db:"bank_card_id"`
	BankCardName         *string    `db:"bank_card_name"`
	BankCardIsCreditCard *bool      `db:"bank_card_is_credit_card"`
	JourneyID            *string    `db:"journey_id"`
	JourneyStartDate     *string    `db:"journey_start_date"`
	JourneyEndDate       *string    `db:"journey_end_date"`
	RegionTitle          *string    `db:"region_title"`
	Latitude             *float64   `db:"latitude"`
	Longitude            *float64   `db:"longitude"`
	FormattedAddress     *string    `db:"formatted_address"`
	Address              *string    `db:"address"`
	Province             *string    `db:"province"`
	City                 *string    `db:"city"`
	District             *string    `db:"district"`
	CreatedByStr         *string    `db:"created_by_str"`
	UpdatedByStr         *string    `db:"updated_by_str"`
	DateCreated          *time.Time `db:"date_created"`
	LastUpdated          *time.Time `db:"last_updated"`
	CreatedBy            *UserBasis
	UpdatedBy            *UserBasis
}

type JourneyIncomeExpenseDetailRepository interface {
	QueryExistingDistinctYears(ctx context.Context, accountGroupID string) ([]int, error)
	Query(ctx context.Context, journeyID string, accountGroupID string, keyword string, pageSize int64, pageIndex int64) ([]JourneyIncomeExpenseDetail, error)
	QueryDetails(ctx context.Context, accountGroupID string, id string) (*JourneyIncomeExpenseFullAddressDetail, error)
	QueryCoordinates(ctx context.Context, accountGroupID string, year string) ([]FormattedAddressCoordinate, error)
	Exists(ctx context.Context, accountGroupID string, journeyID string) (bool, error)
	Insert(ctx context.Context, model *dbmodel.JourneyIncomeExpenseDetail) error
	Update(ctx context.Context, accountGroupID string, model *dbmodel.JourneyIncomeExpenseDetail) error
	Delete(ctx context.Context, accountGroupID string, ids []string) error
	DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error
}

func NewJourneyIncomeExpenseDetailRepository(db *sql.DB) JourneyIncomeExpenseDetailRepository {
	return &journeyIncomeExpenseDetailRepository{db: db}
}

// QueryExistingDistinctYears queries all distinct income/expense years.
func (r *journeyIncomeExpenseDetailRepository) QueryExistingDistinctYears(ctx context.Context, accountGroupID string) ([]int, error) {
	query := []int{}

	stmt := jet.
		SELECT(
			jet.DISTINCT(jet.EXTRACT(jet.YEAR, tbJourenyDetail.IncomeExpenseDate)),
		).
		FROM(
			tbJourenyDetail,
		).
		WHERE(
			tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	err := stmt.QueryContext(ctx, r.db, &query)

	return query, err
}

// Queries all journey income/expense which associated with provided account group and keyword.
func (r *journeyIncomeExpenseDetailRepository) Query(ctx context.Context, journeyID string, accountGroupID string, keyword string, pageSize int64, pageIndex int64) ([]JourneyIncomeExpenseDetail, error) {
	var query []struct {
		ID                   string    `db:"id"`
		IncomeExpenseDate    time.Time `db:"income_expense_date"`
		Location             string    `db:"location"`
		Currency             string    `db:"currency"`
		Amount               float64   `db:"amount"`
		Type                 string    `db:"type"`
		PaymentType          string    `db:"payment_type"`
		Details              string    `db:"details"`
		BankID               *string   `db:"bank_id"`
		BankShortName        *string   `db:"bank_short_name"`
		BankCardID           *string   `db:"bank_card_id"`
		BankCardName         *string   `db:"bank_card_name"`
		BankCardIsCreditCard *bool     `db:"bank_card_is_credit_card"`
		JourneyID            *string   `db:"journey_id"`
		JourneyStartDate     *string   `db:"journey_start_date"`
		JourneyEndDate       *string   `db:"journey_end_date"`
		RegionTitle          *string   `db:"region_title"`
	}

	selections := jet.ProjectionList{
		tbJourenyDetail.IncomeExpenseDate.AS("income_expense_date"),
		tbJourenyDetail.Location.AS("location"),
		tbJourenyDetail.Currency.AS("currency"),
		tbJourenyDetail.Amount.AS("amount"),
		tbJourenyDetail.Type.AS("type"),
		tbJourenyDetail.PaymentType.AS("payment_type"),
		tbJourenyDetail.Details.AS("details"),
		tbBank.ID.AS("bank_id"),
		tbBank.ShortName.AS("bank_short_name"),
		tbBankCard.ID.AS("bank_card_id"),
		tbBankCard.Name.AS("bank_card_name"),
		tbBankCard.IsCreditCard.AS("bank_card_is_credit_card"),
	}

	from := tbJourenyDetail.
		LEFT_JOIN(tbBankCard, tbBankCard.ID.EQ(tbJourenyDetail.BankCardID)).
		LEFT_JOIN(tbBank, tbBank.ID.EQ(tbBankCard.BankID))

	if !util.IsEmptyString(keyword) {
		selections = append(selections, jet.ProjectionList{
			tbJoureny.ID.AS("journey_id"),
			tbJoureny.StartDate.AS("journey_start_date"),
			tbJoureny.EndDate.AS("journey_end_date"),
			tbRegion.Title.AS("region_title"),
		}...)

		from = from.INNER_JOIN(tbJoureny, tbJoureny.ID.EQ(tbJourenyDetail.JourneyID)).
			INNER_JOIN(tbRegion, tbRegion.ID.EQ(tbJoureny.RegionID))
	}

	stmt := jet.
		SELECT(
			tbJourenyDetail.ID.AS("id"),
			selections...,
		).
		FROM(from)

	keywordRegx := regexp.MustCompile(`\s+`)
	keywords := keywordRegx.Split(keyword, -1)

	conditions := []jet.BoolExpression{
		tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)),
	}

	if !util.IsEmptyString(journeyID) {
		conditions = append(conditions, tbJourenyDetail.JourneyID.EQ(jet.String(journeyID)))
	}

	// Searchs with AND condition for multiple keywords we just need to wrap each keyword into one OR section.
	// The keyword supports separated by blank string.
	// When it occurs we are splitting the keyword with blank string and gets multiple keywords.
	// The multiple keywords will be used to search within OR condition.
	for _, keyword := range keywords {
		if !util.IsEmptyString(keyword) {
			conditions = append(conditions,
				jet.OR(
					tbJourenyDetail.Details.LIKE(jet.String("%"+keyword+"%")),
					tbJourenyDetail.Remark.LIKE(jet.String("%"+keyword+"%")),
				),
			)
		}
	}

	stmt = stmt.WHERE(jet.AND(conditions...))

	if pageSize > 0 {
		stmt = stmt.LIMIT(pageSize).
			OFFSET(pageSize * pageIndex)
	}

	stmt = stmt.ORDER_BY(
		tbJourenyDetail.IncomeExpenseDate.DESC(),
		tbJourenyDetail.Type.DESC(),
		tbJourenyDetail.BankCardID.ASC(),
		tbJourenyDetail.LastUpdated.DESC(),
	)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]JourneyIncomeExpenseDetail, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// QueryDetails queries journey income/expense detail by id and account group id.
func (r *journeyIncomeExpenseDetailRepository) QueryDetails(ctx context.Context, accountGroupID string, id string) (*JourneyIncomeExpenseFullAddressDetail, error) {
	var query struct {
		ID                   string     `db:"id"`
		IncomeExpenseDate    time.Time  `db:"income_expense_date"`
		Location             string     `db:"location"`
		Currency             string     `db:"currency"`
		Amount               float64    `db:"amount"`
		Type                 string     `db:"type"`
		PaymentType          string     `db:"payment_type"`
		Details              string     `db:"details"`
		BankID               *string    `db:"bank_id"`
		BankShortName        *string    `db:"bank_short_name"`
		BankCardID           *string    `db:"bank_card_id"`
		BankCardName         *string    `db:"bank_card_name"`
		BankCardIsCreditCard *bool      `db:"bank_card_is_credit_card"`
		JourneyID            *string    `db:"journey_id"`
		JourneyStartDate     *string    `db:"journey_start_date"`
		JourneyEndDate       *string    `db:"journey_end_date"`
		RegionTitle          *string    `db:"region_title"`
		Latitude             *float64   `db:"latitude"`
		Longitude            *float64   `db:"longitude"`
		FormattedAddress     *string    `db:"formatted_address"`
		Address              *string    `db:"address"`
		Province             *string    `db:"province"`
		City                 *string    `db:"city"`
		District             *string    `db:"district"`
		CreatedByStr         *string    `db:"created_by_str"`
		UpdatedByStr         *string    `db:"updated_by_str"`
		DateCreated          *time.Time `db:"date_created"`
		LastUpdated          *time.Time `db:"last_updated"`
		CreatedBy            *UserBasis
		UpdatedBy            *UserBasis
	}

	stmt := jet.
		SELECT(
			tbJourenyDetail.ID.AS("id"),
			tbJourenyDetail.JourneyID.AS("journey_id"),
			tbJourenyDetail.IncomeExpenseDate.AS("income_expense_date"),
			tbJourenyDetail.Location.AS("location"),
			tbJourenyDetail.Currency.AS("currency"),
			tbJourenyDetail.Amount.AS("amount"),
			tbJourenyDetail.Type.AS("type"),
			tbJourenyDetail.PaymentType.AS("payment_type"),
			tbBank.ID.AS("bank_id"),
			tbBank.ShortName.AS("bank_short_name"),
			tbBankCard.ID.AS("bank_card_id"),
			tbBankCard.Name.AS("bank_card_name"),
			tbJourenyDetail.Details.AS("details"),
			tbJourenyDetail.Remark.AS("remark"),
			tbJourenyDetail.Latitude.AS("latitude"),
			tbJourenyDetail.Longitude.AS("longitude"),
			tbJourenyDetail.Address.AS("address"),
			tbJourenyDetail.FormattedAddress.AS("formatted_address"),
			tbJourenyDetail.Province.AS("province"),
			tbJourenyDetail.City.AS("city"),
			tbJourenyDetail.District.AS("district"),
			tbJourenyDetail.CreatedBy.AS("created_by_str"),
			tbJourenyDetail.UpdatedBy.AS("updated_by_str"),
			tbJourenyDetail.DateCreated.AS("date_created"),
			tbJourenyDetail.LastUpdated.AS("last_updated"),
		).
		FROM(
			tbJourenyDetail.
				LEFT_JOIN(tbBankCard, tbBankCard.ID.EQ(tbJourenyDetail.BankCardID)).
				LEFT_JOIN(tbBank, tbBank.ID.EQ(tbBankCard.BankID)),
		).
		WHERE(
			tbJourenyDetail.ID.EQ(jet.String(id)).
				AND(tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID))),
		)

	if err := stmt.QueryContext(ctx, r.db, &query); err != nil {
		if errors.Is(err, qrm.ErrNoRows) {
			return nil, nil
		}

		return nil, err
	}

	return (*JourneyIncomeExpenseFullAddressDetail)(&query), nil
}

// QueryCoordinates queries coordinates by account group id and year.
func (r *journeyIncomeExpenseDetailRepository) QueryCoordinates(ctx context.Context, accountGroupID string, year string) ([]FormattedAddressCoordinate, error) {
	var (
		yearInt int64
		err     error
	)

	if util.IsEmptyString(year) {
		yearInt = 0
	} else {
		yearInt, err = util.StringToInt64(year)

		if err != nil {
			return nil, err
		}
	}

	// Locations contain same formatted address but different coordinates which we like to take only one match.
	// https://www.navicat.com/en/company/aboutus/blog/1647-applying-select-distinct-to-one-column-only
	subQuery := jet.
		SELECT(
			jet.MAX(tbJourenyDetail.ID),
		).
		FROM(
			tbJourenyDetail,
		).
		WHERE(
			tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbJourenyDetail.Type.EQ(jet.String(constant.IncomeExpenseTypeExpense))).
				AND(tbJourenyDetail.Latitude.IS_NOT_NULL()).
				AND(tbJourenyDetail.Longitude.IS_NOT_NULL()).
				AND(tbJourenyDetail.FormattedAddress.IS_NOT_NULL()).
				AND(tbJourenyDetail.FormattedAddress.NOT_EQ(jet.String(""))).
				AND(
					jet.Int(yearInt).EQ(jet.Int(0)).
						OR(jet.EXTRACT(jet.YEAR, tbJourenyDetail.IncomeExpenseDate).EQ(jet.Int(yearInt))),
				),
		).
		GROUP_BY(
			tbJourenyDetail.FormattedAddress,
		)

	var query []struct {
		Latitude         *float64 `db:"latitude"`
		Longitude        *float64 `db:"longitude"`
		FormattedAddress *string  `db:"formatted_address"`
	}

	stmt := jet.
		SELECT(
			tbJourenyDetail.FormattedAddress.AS("formatted_address"),
			tbJourenyDetail.Latitude.AS("latitude"),
			tbJourenyDetail.Longitude.AS("longitude"),
		).
		FROM(
			tbJourenyDetail,
		).
		WHERE(
			tbJourenyDetail.ID.IN(subQuery),
		)

	if err = stmt.QueryContext(ctx, r.db, &query); err != nil {
		return nil, err
	}

	result := make([]FormattedAddressCoordinate, 0, len(query))

	for _, item := range query {
		result = append(result, item)
	}

	return result, nil
}

// Exists checks if the JourneyIncomeExpenseDetail exists by accountGroup id and journey id.
func (r *journeyIncomeExpenseDetailRepository) Exists(ctx context.Context, accountGroupID string, journeyID string) (bool, error) {
	condition := tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
		AND(tbJourenyDetail.JourneyID.EQ(jet.String(journeyID)))

	return db.Exists(ctx, r.db, tbJourenyDetail, condition)
}

// Insert inserts new model.
func (r *journeyIncomeExpenseDetailRepository) Insert(ctx context.Context, model *dbmodel.JourneyIncomeExpenseDetail) error {
	stmt := tbJourenyDetail.
		INSERT().
		MODEL(model)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// Update updates existing model.
func (r *journeyIncomeExpenseDetailRepository) Update(ctx context.Context, accountGroupID string, model *dbmodel.JourneyIncomeExpenseDetail) error {
	stmt := tbJourenyDetail.
		UPDATE(
			tbJourenyDetail.BankCardID,
			tbJourenyDetail.Type,
			tbJourenyDetail.PaymentType,
			tbJourenyDetail.Details,
			tbJourenyDetail.IncomeExpenseDate,
			tbJourenyDetail.Currency,
			tbJourenyDetail.Amount,
			tbJourenyDetail.Remark,
			tbJourenyDetail.Province,
			tbJourenyDetail.City,
			tbJourenyDetail.District,
			tbJourenyDetail.Address,
			tbJourenyDetail.FormattedAddress,
			tbJourenyDetail.Latitude,
			tbJourenyDetail.Longitude,
			tbJourenyDetail.LastUpdated,
			tbJourenyDetail.UpdatedBy,
		).
		MODEL(model).
		WHERE(
			tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
				AND(tbJourenyDetail.ID.EQ(jet.String(model.ID))),
		)

	_, err := stmt.ExecContext(ctx, r.db)

	return err
}

// Delete deletes the IncomeExpenseDetails with provided account group id and ids.
func (r *journeyIncomeExpenseDetailRepository) Delete(ctx context.Context, accountGroupID string, ids []string) error {
	if len(ids) > 0 {
		idsExpression := db.SliceExpression(ids)

		stmt := tbJourenyDetail.
			DELETE().
			WHERE(
				tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)).
					AND(tbJourenyDetail.ID.IN(idsExpression...)),
			)

		_, err := stmt.ExecContext(ctx, r.db)

		return err
	}

	return nil
}

// DeleteWithTx deletes all income and expense details by account group id with transaction.
func (*journeyIncomeExpenseDetailRepository) DeleteWithTx(ctx context.Context, tx *sql.Tx, accountGroupID string) error {
	stmt := tbJourenyDetail.
		DELETE().
		WHERE(
			tbJourenyDetail.AccountGroupID.EQ(jet.String(accountGroupID)),
		)

	_, err := stmt.ExecContext(ctx, tx)

	return err
}

type journeyIncomeExpenseDetailRepository struct {
	db *sql.DB
}
